import java.util.ArrayList;
import java.util.EnumMap;
import java.sql.*;

import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.DocumentBuilder;

import org.w3c.dom.Document;
import org.w3c.dom.NodeList;
import org.w3c.dom.Node;
import org.w3c.dom.Element;

import java.io.File;


public class Database {
	
	public enum ORE{ GOLD, TIN, IRON };
	private EnumMap<ORE, Integer> mapOfOres;
	private ArrayList<TraderInfo> traders;
	private ArrayList<String> database,ores,validTraders;
	private Connection connection;
	
	public Database(){
		//hard coded dummy data
		traders = new ArrayList<TraderInfo>();
		
		traders.add(new TraderInfo("Mark", 200));
		traders.get(0).addOre("GOLD", 1000);
		traders.get(0).addOre("TIN", 500);
		
		traders.add(new TraderInfo("Rene", 1000000));
		
		traders.add(new TraderInfo("David", 0));
		traders.get(1).addOre("IRON", 10000);
		traders.get(1).addOre("GOLD", 500);	
		
		mapOfOres = new EnumMap<ORE, Integer>(ORE.class);
		
		try {
			Class.forName("org.sqlite.JDBC");
			connection = DriverManager.getConnection("jdbc:sqlite:test.db");
			connection.close();
		}//end try block
	 	catch (ClassNotFoundException e) {
			System.out.println("Error while creating database.");
			e.printStackTrace();
		}//end catch block
	 	catch (SQLException e) {
			System.out.println("Problem connecting to database during creation.");
			e.printStackTrace();
		}//end catch block
	}
	
	public void addTrader(String _trader) {
		traders.add(new TraderInfo(_trader, 0));
	}
	
	public void addTrader(String _trader, int _money, ArrayList<Database.ORE> _oreNames, ArrayList<Integer> _oreValues) {
		TraderInfo t = new TraderInfo(_trader, _money);
		traders.add(t);
		for (Database.ORE o : _oreNames) {
			traders.get(traders.indexOf(t)).addOre(o.toString(), _oreValues.get(_oreNames.indexOf(o)));
		}
	}
	
	public boolean containsOre(ORE _ore) {
		return mapOfOres.containsKey(_ore);
	}

	public boolean containsTrader(String name){
		for (TraderInfo t : traders) {
			if (t.traderID.equals(name)) {
				return true;
			}
		}
		return false;
	}
	
	public void removeTrader(String _trader) {
		for (TraderInfo t : traders) {
			if (t.traderID.equals(_trader)) {
				traders.remove(t);
				return;
			}
		}
	}
	
	public void auditParser()
    {
            try {
                    File fXmlFile = new File("resources/myxmldailyupload.xml");
                    DocumentBuilderFactory dbFactory = DocumentBuilderFactory.newInstance();
                    DocumentBuilder dBuilder = dbFactory.newDocumentBuilder();
                    Document doc = dBuilder.parse(fXmlFile);
                    
                    doc.getDocumentElement().normalize();
                    
                    System.out.println("Root element : " + doc.getDocumentElement().getNodeName());
                    
                    ArrayList<String> uniqueTraders = new ArrayList<String>();
                    ArrayList<String> uniqueOres = new ArrayList<String>();
                    
                    NodeList oList = doc.getElementsByTagName("ore");
                    NodeList tiList = doc.getElementsByTagName("traderinfo");
                    NodeList duList = doc.getElementsByTagName("dailyupload");
                    
                    System.out.println("-----------------------------");
//                  ========================================================================================
//                  Loops through all of the ores in the daily xml upload file
                    
                    for (int temp = 0; temp < oList.getLength()-1; temp++) {
                             
                            Node oNode = oList.item(temp);
             
                            System.out.println("\nCurrent Element :" + oNode.getNodeName());
             
                            if (oNode.getNodeType() == Node.ELEMENT_NODE) {
             
                                    Element eElement = (Element) oNode;
                                    
                                    if(!uniqueOres.contains(eElement.getElementsByTagName("orename").item(0).getTextContent()))
                                    {
                                            uniqueOres.add(eElement.getElementsByTagName("orename").item(0).getTextContent());
                                    }
                                    
                                    System.out.println("orename : " + eElement.getElementsByTagName("orename").item(0).getTextContent());
                                    System.out.println("orequantity : " + eElement.getElementsByTagName("orequantity").item(0).getTextContent());            
                            }
                    }
                    
                    ores=uniqueOres;
//                  ========================================================================================
//                  Loops through all of the traderinfo in the daily xml upload file
                    for (int temp = 0; temp < tiList.getLength(); temp++) {
                             
                            Node tiNode = tiList.item(temp);
             
                            System.out.println("\nCurrent Element :" + tiNode.getNodeName());
             
                            if (tiNode.getNodeType() == Node.ELEMENT_NODE) {
             
                                    Element eElement = (Element) tiNode;
                                    
                                    uniqueTraders.add(eElement.getElementsByTagName("tradername").item(0).getTextContent());
             
                                    System.out.println("tradername : " + eElement.getElementsByTagName("tradername").item(0).getTextContent());
                                    System.out.println("cash : " + eElement.getElementsByTagName("cash").item(0).getTextContent());
                                    System.out.println("ores : " + eElement.getElementsByTagName("ores").item(0).getTextContent());
                            }
                    }
                    validTraders=uniqueTraders;
//                  ========================================================================================
//                  Loops through all of the dailyupload in the xml upload file
                    
                    for (int temp = 0; temp < duList.getLength(); temp++) {
                             
                            Node duNode = duList.item(temp);
             
                            System.out.println("\nCurrent Element :" + duNode.getNodeName());
             
                            if (duNode.getNodeType() == Node.ELEMENT_NODE) {
             
                                    Element eElement = (Element) duNode;
                                    
                                    //TODO Here you should add code to store this information so it can be added to the database.
             
                                    System.out.println("traderinfo : " + eElement.getElementsByTagName("traderinfo").item(0).getTextContent());
                                    System.out.println("ore : " + eElement.getElementsByTagName("ore").item(0).getTextContent());
                            }
                    }
                    
//                  System.out.println("#1 normal for loop");
//                  for (int i = 0; i < uniqueOres.size(); i++) {
//                          System.out.println(uniqueOres.get(i));
//                  }
//                  
//                  System.out.println("#2 normal for loop");
//                  for (int i = 0; i < uniqueTraders.size(); i++) {
//                          System.out.println(uniqueTraders.get(i));
//                  }
            } catch (Exception e) {
                    e.printStackTrace();
                    
            }
    }
	
	public TraderInfo getTrader(String name){
		for (TraderInfo t : traders) {
			if (t.traderID.compareTo(name) == 0){
				return t;
			}
		}
		return null;
	}
	
	public int maximumMoney() {
		int max = 0;
		for (TraderInfo t : traders) {
			if (t.fundsAvailable > max)
				max = t.fundsAvailable;
		}
		return max;
	}
	
	public int amountOfTradersOre(String _trader, ORE _ore) {
		TraderInfo t = getTrader(_trader);
		int oreIndex = t.ores.indexOf(_ore.toString());
		return t.oreAvailable.get(oreIndex);
	}
	
	/**
	 * This method is not complete. It adds a new offer to the database.
	 * 
	 * The code to add a new offer needs to be complete, but opening and connecting
	 * to the database is already complete.
	 */
	public void addOffer()
     {
		 try {
             Class.forName("org.sqlite.JDBC");
             connection = DriverManager.getConnection("jdbc:sqlite:test.db");
             Statement statement = connection.createStatement();
             
             //TODO put code to add a new offer to the database here
             
             connection.close();
		 	}//end try block
		 catch (ClassNotFoundException e) {
             System.out.println("Error while opening database to update Offer Table.");
             e.printStackTrace();
		 }//end catch block
		 catch (SQLException e) {
             System.out.println("Problem connecting to database during Offer Table update.");
             e.printStackTrace();
		 }//end catch block  
     }
     
     /**
      * This method is not complete. It removes an offer and its information from the database
      * 
      * The code to remove the offer from the database needs to be complete. The code to open
      * and connect to the database is already complete.
      * 
      * @param offerID The id of the offer obtained from the trader system
      */
	 public void removeOffer(String offerID)
     {
    	 try {
             Class.forName("org.sqlite.JDBC");
             connection = DriverManager.getConnection("jdbc:sqlite:test.db");
             Statement statement = connection.createStatement();
             
            //TODO Put code to remove the offer from the database here
             
             connection.close();
    	 }//end try block
    	 catch (ClassNotFoundException e) {
             System.out.println("Error while opening database to update Offer Table.");
             e.printStackTrace();
    	 }//end catch block
    	 catch (SQLException e) {
             System.out.println("Problem connecting to database during Offer Table update.");
             e.printStackTrace();
    	 }//end catch block    
     }
     
     /**
      * This method is not complete. It creates a table to contain the information recieved
      * from the audit system at the beginning of the day.
      * 
      * Columns need to be added to the table, and it needs to be populated.
      */
     public void updateAuditTable(){
             try {
                     Class.forName("org.sqlite.JDBC");
                     connection = DriverManager.getConnection("jdbc:sqlite:test.db");
                     Statement statement = connection.createStatement();
                     statement.executeUpdate("drop table if exists audit;");
                     
                     //TODO list the columns to be in the audit table in parentheses at the end of the following command
                     statement.executeUpdate("create table audit");
                     
                     //TODO populate the table with appropriate information
                     
                     connection.close();
             }//end try block
             catch (ClassNotFoundException e) {
                     System.out.println("Error while opening database to update Audit Table.");
                     e.printStackTrace();
             }//end catch block
             catch (SQLException e) {
                     System.out.println("Problem connecting to database during Audit Table update.");
                     e.printStackTrace();
             }//end catch block
     }
     
     /**
      * This code is partially complete. It currently creates a new table (deleting the 
      * old/previous one in the process) with columns for the traderID, traderName, funds
      * as well as one column for each unique ore that is valid to trade for the day.
      * 
      * The code to be completed is to populate the table with the traderID, traderName, and
      * amounts of funds and ores for each column in the database. This table will be used 
      * to keep track of each valid trader in the database, and the current funds/ores that
      * they have and in what quantities, that are currently not tied up an any offers and
      * available to be offer/used to accept an offer.
      */
     public void updateTemporaryTable(){
    	 String columns = "traderID,traderName, funds";
    	 for(int index=0;index<ores.size();index++)
    	 {
    		 columns = columns + ores.get(index);
    		 if(index!=(ores.size()-1))
    		 {
    			 columns = columns + ",";
    		 }//end if statement
    	 }//end for loop
             try {
                     Class.forName("org.sqlite.JDBC");
                     connection = DriverManager.getConnection("jdbc:sqlite:test.db");
                     Statement statement = connection.createStatement();
                     statement.executeUpdate("drop table if exists temporary;");
                     statement.executeUpdate("create table temporary(columns);");
                     
                     //TODO Put code to populate table initially here
                     
                     connection.close();
             }//end try block
             catch (ClassNotFoundException e) {
                     System.out.println("Error while opening database to populate Temporary Table.");
                     e.printStackTrace();
             }//end catch block
             catch (SQLException e) {
                     System.out.println("Problem connecting to database during Temporary Table population.");
                     e.printStackTrace();
             }//end catch block
             
     }
     
     /**
      * This method is not complete. It is to update the current temporary table of
      * the current usable resources and funds for each trader (more info on table above).
      * This method takes in the unique id of the trader, the resource to be modified,
      * which can also be the funds. This resource needs to match the name of the column being 
      * modified in the table. It also takes in the new amount as an int. It updates the column
      * matching the resource parameter in the row matching the traderID to the newAmount parameter
      * 
      * The functionality of actually updating this specific field is not complete
      */
     public void updateTemporaryTable(String traderID, String resource, int newAmount)
     {
             try {
                     Class.forName("org.sqlite.JDBC");
                     connection = DriverManager.getConnection("jdbc:sqlite:test.db");
                     
                     //TODO put code to modify table with new data here
                     
                     connection.close();
             }//end try block
             catch (ClassNotFoundException e) {
                     System.out.println("Error while opening database to update Temporary Table.");
                     e.printStackTrace();
             }//end catch block
             catch (SQLException e) {
                     System.out.println("Problem connecting to database during Temporary Table update.");
                     e.printStackTrace();
             }//end catch block
     }

     /**
      * This method is complete. It creates a new offer table, populated with no data,
      * so it should be called at the beginning of each day at the start of the trading
      * period. The table holds the offer id, the id and name of the trader, whether
      * the offer is a buy or sell, the type of ore and amount, and the price.
      */
     public void updateOfferTable(){
             try {
                     Class.forName("org.sqlite.JDBC");
                     connection = DriverManager.getConnection("jdbc:sqlite:test.db");
                     Statement statement = connection.createStatement();
                     statement.executeUpdate("drop table if exists offer;");
                     statement.executeUpdate("create table offer (offerID,traderID,traderName,offerType,oreName,oreAmount,price");
                     connection.close();
             }//end try block
             catch (ClassNotFoundException e) {
                     System.out.println("Error while opening database to update Offer Table.");
                     e.printStackTrace();
             }//end catch block
             catch (SQLException e) {
                     System.out.println("Problem connecting to database during Offer Table update.");
                     e.printStackTrace();
             }//end catch block
     }
}
